{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![](https://pptwinpics.oss-cn-beijing.aliyuncs.com/CDA%E8%AE%B2%E5%B8%88%E6%B0%B4%E5%8D%B0_20200314161940.png)\n",
    "# xlwings 操作 Excel 系统课程"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 打开新建 Excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 引入库，就是加载魔法\n",
    "import xlwings as xw\n",
    "\n",
    "# 打开Excel程序，默认设置：程序可见，只打开不新建工作薄\n",
    "# app = xw.App(visible=True, add_book=False)\n",
    "# 新建工作簿 (如果不接下一条代码的话，Excel只会一闪而过，卖个萌就走了）\n",
    "# workbook = app.books.add()\n",
    "\n",
    "# 打开已有Excel（支持绝对路径和相对路径）\n",
    "# wb = app.books.open('example.xlsx')\n",
    "\n",
    "#练习的时候建议直接用下面这条\n",
    "wb = xw.Book('example.xlsx')\n",
    "#这样的话就不会频繁打开新的Excel"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 关闭退出 Excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 保存工作簿\n",
    "wb.save('example.xlsx')\n",
    "\n",
    "# 退出工作簿（可省略）\n",
    "wb.close()\n",
    "\n",
    "#退出Excel\n",
    "app.quit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 引用 Excel 工作表和单元格"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 引用工作表\n",
    "sht = wb.sheets[0]\n",
    "#sht = wb.sheets[第一个sheet名]\n",
    "\n",
    "# 引用单元格\n",
    "\n",
    "rng = sht.range('a1')\n",
    "#rng = sht['a1']\n",
    "#rng = sht[0,0] 第一行的第一列即a1,相当于pandas的切片\n",
    "\n",
    "# 引用区域\n",
    "rng = sht.range('a1:a5')\n",
    "#rng = sht['a1:a5']\n",
    "#rng = sht[:5,0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 写入数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'sht' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-34-e3bb03eb5a9d>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      3\u001b[0m \u001b[0;31m# 选择起始单元格A1,写入字符串‘Hello’\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      4\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 5\u001b[0;31m \u001b[0msht\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrange\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'a1'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mvalue\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m'Hello'\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      6\u001b[0m \u001b[0;31m# 默认按行插入：A1:D1分别写入1,2,3,4\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      7\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mNameError\u001b[0m: name 'sht' is not defined"
     ]
    }
   ],
   "source": [
    "# (xlwings多个单元格的写入大多是以表格形式)\n",
    "\n",
    "# 选择起始单元格A1,写入字符串‘Hello’\n",
    "sht.range('a1').value = 'Hello'\n",
    "\n",
    "# 默认按行插入：A1:D1分别写入1,2,3,4\n",
    "sht.range('a1').value = [1,2,3,4]\n",
    "\n",
    "# 等同于\n",
    "sht.range('a1:d1').value = [1,2,3,4]\n",
    "\n",
    "# 按列插入： A2:A5分别写入5,6,7,8\n",
    "# 你可能会想\n",
    "*sht.range('a2:a5').value = [5,6,7,8]\n",
    "\n",
    "# 但是你会发现xlwings还是会按行处理的，上面一行等同于\n",
    "*sht.range('a2').value = [5,6,7,8]\n",
    "\n",
    "# 正确语法:\n",
    "sht.range('a2').options(transpose=True).value = [5,6,7,8]\n",
    "\n",
    "# 既然默认的是按行写入，我们就把它倒过来嘛（transpose），单词要打对，如果你打错单词，它不会报错，而会按默认的行来写入（别问我怎么知道的）\n",
    "\n",
    "# 我们输入信息的时候往往不只是写入一行或一列，\n",
    "# 多行输入就要用二维列表了：\n",
    "\n",
    "sht.range('a6').expand('table').value = [['a','b','c'],['d','e','f'],['g','h','i']]\n",
    "# 说完了写入就该讲读取了，记住了写入，读取就简单了"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 读取数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "ename": "SyntaxError",
     "evalue": "starred assignment target must be in a list or tuple (<ipython-input-35-34a87cf8332e>, line 17)",
     "output_type": "error",
     "traceback": [
      "\u001b[0;36m  File \u001b[0;32m\"<ipython-input-35-34a87cf8332e>\"\u001b[0;36m, line \u001b[0;32m17\u001b[0m\n\u001b[0;31mSyntaxError\u001b[0m\u001b[0;31m:\u001b[0m starred assignment target must be in a list or tuple\n"
     ]
    }
   ],
   "source": [
    "# 读取A1:D4（直接填入单元格范围就行了）\n",
    "\n",
    "print(sht.range('a1:d4').value)\n",
    "# 返回的值是列表形式，多行多列为二维列表，但有一点要注意，返回的数值默认是浮点数\n",
    "\n",
    "a = sht.range('a1:d1').value\n",
    "print(a)\n",
    "for i in a:\n",
    "  print(i)\n",
    "  print(type(i))\n",
    "    \n",
    "# 读取excel的第一列怎么做？\n",
    "\n",
    "*a = sht.range('a:a').value\n",
    "print(len(a))\n",
    "\n",
    "\n",
    "# 你将会得到一个1048576个元素的列表，也就是空值也包含进去了，所以这种方法不行\n",
    "\n",
    "# 思路：先计算单元格的行数(前提是连续的单元格)\n",
    "\n",
    "rng = sht.range('a1').expand('table')\n",
    "nrows = rng.rows.count\n",
    "# 接着就可以按准确范围读取了\n",
    "\n",
    "a = sht.range(f'a1:a{nrows}').value\n",
    "# 同理选取一行的数据也一样\n",
    "\n",
    "ncols = rng.columns.count\n",
    "#用切片\n",
    "fst_col = sht[0,:ncols].value\n",
    "# 好了基本操作就介绍到这里了，下次搞个实战，把xlwings好好用起来"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "这次我们用xlwings来实现：\n",
    "- 保存数据\n",
    "- 更新数据\n",
    "假设我们爬取到了快递信息，并且已经处理成了二维列表的形式以便处理（非本文重点不细说）：\n",
    "\n",
    "# 1.保存数据\n",
    "https://zhuanlan.zhihu.com/p/54143277"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "ename": "IndexError",
     "evalue": "Index 0 out of range (0 elements)",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mIndexError\u001b[0m                                Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-36-0d5b834dc3e7>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mxlwings\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0mxw\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 2\u001b[0;31m \u001b[0mwb\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mxw\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mBook\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      3\u001b[0m \u001b[0msht\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mwb\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msheets\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      4\u001b[0m info_list = [['20190001','已揽收','凯撒邮局'],\n\u001b[1;32m      5\u001b[0m \u001b[0;34m[\u001b[0m\u001b[0;34m'20190001'\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m'已发货'\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m'凯撒邮局'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/xlwings/main.py\u001b[0m in \u001b[0;36m__init__\u001b[0;34m(self, fullname, update_links, read_only, format, password, write_res_password, ignore_read_only_recommended, origin, delimiter, editable, notify, converter, add_to_mru, local, corrupt_load, impl)\u001b[0m\n\u001b[1;32m    530\u001b[0m                 \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    531\u001b[0m                     \u001b[0mapp\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mApp\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 532\u001b[0;31m                     \u001b[0mimpl\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mapp\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mbooks\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mimpl\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    533\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    534\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mimpl\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mimpl\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/xlwings/main.py\u001b[0m in \u001b[0;36m__getitem__\u001b[0;34m(self, key)\u001b[0m\n\u001b[1;32m     66\u001b[0m             \u001b[0ml\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mlen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     67\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mkey\u001b[0m \u001b[0;34m>=\u001b[0m \u001b[0ml\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 68\u001b[0;31m                 \u001b[0;32mraise\u001b[0m \u001b[0mIndexError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"Index %s out of range (%s elements)\"\u001b[0m \u001b[0;34m%\u001b[0m \u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0ml\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     69\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mkey\u001b[0m \u001b[0;34m<\u001b[0m \u001b[0;36m0\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     70\u001b[0m                 \u001b[0;32mif\u001b[0m \u001b[0mkey\u001b[0m \u001b[0;34m<\u001b[0m \u001b[0;34m-\u001b[0m\u001b[0ml\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mIndexError\u001b[0m: Index 0 out of range (0 elements)"
     ]
    }
   ],
   "source": [
    "import xlwings as xw\n",
    "wb = xw.Book()\n",
    "sht = wb.sheets[0]\n",
    "info_list = [['20190001','已揽收','凯撒邮局'],\n",
    "['20190001','已发货','凯撒邮局'],\n",
    "['20192288','已揽收','麻花镇邮局'],\n",
    "['20192288','已发货','麻花镇邮局'],\n",
    "['20192288','正在派送','阿里山']]\n",
    "# 首先，写入表头，\n",
    "\n",
    "titles = [['包裹号','状态','地点']]\n",
    "sht.range('a1').value = titles\n",
    "# 然后写入轨迹信息\n",
    "\n",
    "sht.range('a2').value = info_list\n",
    "# 保存\n",
    "\n",
    "wb.save('Track.xlsx')\n",
    "# 这样，第一步保存数据就完成了"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2.更新数据\n",
    "\n",
    "\n",
    "\n",
    "我们第二天又爬取了一次信息，发现信息更新了：\n",
    "\n",
    "[\n",
    "\n",
    "['20190001','已揽收','凯撒邮局'],\n",
    "\n",
    "['20190001','已发货','凯撒邮局'],\n",
    "\n",
    "['20190001','正在派送','王村村口'],\n",
    "\n",
    "['20190001','已签收','老王家'],\n",
    "\n",
    "['20192288','已揽收','麻花镇邮局'],\n",
    "\n",
    "['20192288','已发货','麻花镇邮局'],\n",
    "\n",
    "['20192288','正在派送','阿里山'],\n",
    "\n",
    "['20192288','已发货','小马家']\n",
    "\n",
    "]\n",
    "\n",
    "更新数据其实没什么难度，直接覆盖写入就好了\n",
    "\n",
    "\n",
    "\n",
    "但是如果我想知道更新了多少条记录怎么办呢？\n",
    "\n",
    "将数据去重，剩下的就是更新的\n",
    "\n",
    "首先读取之前写入的信息："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import xlwings as xw\n",
    "\n",
    "wb = xw.Book('Track.xlsx')\n",
    "sht = wb.sheets[0]\n",
    "first = sht.range('a2').expand('table').value\n",
    "print(first)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "乍一看没什么问题，仔细一看，包裹号都成了浮点数！写入的时候是字符串，读取出来就成了浮点数，所以这时候去重，由于数据类型不一致，无法真正去重。\n",
    "\n",
    "\n",
    "\n",
    "思路一：直接转化数据类型，将每个列表的第一个元素转为整数，再转为字符串"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'first' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-37-6b6b47d85fc1>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0;32mfor\u001b[0m \u001b[0mi\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mfirst\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      2\u001b[0m         \u001b[0mi\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mstr\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mround\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mi\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      3\u001b[0m         \u001b[0mfirst_str\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mappend\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mi\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      4\u001b[0m \u001b[0mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mfirst_str\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mNameError\u001b[0m: name 'first' is not defined"
     ]
    }
   ],
   "source": [
    "for i in first:\n",
    "        i[0] = str(round(i[0]))\n",
    "        first_str.append(i)\n",
    "print(first_str)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "思路二：如果大家对Excel熟悉的话，就会知道，在数字前面加一个英文字符的单引号（'），数字就变成文本格式了，所以我们可以在写入信息的时候加上一个单引号，这样Excel就不会乱改格式了。（以后可能会写一些Excel方面的东西）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "ename": "FileNotFoundError",
     "evalue": "No such file: 'track.xlsx'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mFileNotFoundError\u001b[0m                         Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-38-9ea7557983f2>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mxlwings\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0mxw\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      2\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 3\u001b[0;31m \u001b[0mwb\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mxw\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mBook\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'Track.xlsx'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      4\u001b[0m \u001b[0msht\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mwb\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msheets\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      5\u001b[0m info_list = [[\"'20190001\",\"已揽收\",\"凯撒邮局\"],\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/xlwings/main.py\u001b[0m in \u001b[0;36m__init__\u001b[0;34m(self, fullname, update_links, read_only, format, password, write_res_password, ignore_read_only_recommended, origin, delimiter, editable, notify, converter, add_to_mru, local, corrupt_load, impl)\u001b[0m\n\u001b[1;32m    519\u001b[0m                     impl = app.books.open(fullname, update_links, read_only, format, password, write_res_password,\n\u001b[1;32m    520\u001b[0m                                           \u001b[0mignore_read_only_recommended\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0morigin\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdelimiter\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0meditable\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mnotify\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mconverter\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 521\u001b[0;31m                                           add_to_mru, local, corrupt_load).impl\n\u001b[0m\u001b[1;32m    522\u001b[0m                 \u001b[0;32melif\u001b[0m \u001b[0mlen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcandidates\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m>\u001b[0m \u001b[0;36m1\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    523\u001b[0m                     \u001b[0;32mraise\u001b[0m \u001b[0mException\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"Workbook '%s' is open in more than one Excel instance.\"\u001b[0m \u001b[0;34m%\u001b[0m \u001b[0mfullname\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/xlwings/main.py\u001b[0m in \u001b[0;36mopen\u001b[0;34m(self, fullname, update_links, read_only, format, password, write_res_password, ignore_read_only_recommended, origin, delimiter, editable, notify, converter, add_to_mru, local, corrupt_load)\u001b[0m\n\u001b[1;32m   2866\u001b[0m         \u001b[0mfullname\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mutils\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mfspath\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mfullname\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2867\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mos\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mpath\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexists\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mfullname\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2868\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0mFileNotFoundError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"No such file: '%s'\"\u001b[0m \u001b[0;34m%\u001b[0m \u001b[0mfullname\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2869\u001b[0m         \u001b[0mfullname\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mos\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mpath\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrealpath\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mfullname\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2870\u001b[0m         \u001b[0m_\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mname\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mos\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mpath\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msplit\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mfullname\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mFileNotFoundError\u001b[0m: No such file: 'track.xlsx'"
     ]
    }
   ],
   "source": [
    "import xlwings as xw\n",
    "\n",
    "wb = xw.Book('Track.xlsx')\n",
    "sht = wb.sheets[0]\n",
    "info_list = [[\"'20190001\",\"已揽收\",\"凯撒邮局\"],\n",
    "[\"'20190001\",\"已发货\",\"凯撒邮局\"],\n",
    "[\"'20192288\",\"已揽收\",\"麻花镇邮局\"],\n",
    "[\"'20192288\",\"已发货\",\"麻花镇邮局\"],\n",
    "[\"'20192288\",\"正在派送\",\"阿里山\"]]\n",
    "sht.range('a2').value = info_list\n",
    "values = sht.range('a2').expand('table').value\n",
    "print(values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 然后开始真正的去重\n",
    "\n",
    "extra = [i for i in second if i not in first_str]\n",
    "print(extra)\n",
    "print(len(extra))\n",
    "# 结果没问题，多出三个轨迹信息"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "为了介绍xlwings的插入功能，我们再来设想这样一种情况：\n",
    "\n",
    "已经有了两个包裹的轨迹情况\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "但是我们得到了20190001包裹的最新情况，需要更新这一个包裹的信息:\n",
    "\n",
    "[\n",
    "\n",
    "[\"20190001\",\"已揽收\",\"凯撒邮局\"],\n",
    "\n",
    "[\"20190001\",\"已发货\",\"凯撒邮局\"],\n",
    "\n",
    "[\"20190001\",\"正在派送\",\"王村村口\"],\n",
    "\n",
    "[\"20190001\",\"已签收\",\"老王家\"]\n",
    "\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 首先，去重\n",
    "\n",
    "extra = [i for i in second if i not in first_str]\n",
    "print(extra)\n",
    "\n",
    "# 显示要更新的就一条\n",
    "# 读取第一列的包裹号\n",
    "rng = sht.range('a1').expand('table')\n",
    "nrows = rng.rows.count\n",
    "row_a = sht.range(f'a1:a{nrows}').value\n",
    "\n",
    "# 找到要更新的包裹号\n",
    "for i in extra:\n",
    "    pkg = i[0]\n",
    "    position = row_a.index(pkg)\n",
    "    print(position)\n",
    "    times = row_a.count(pkg)\n",
    "    print(times)\n",
    "\n",
    "# position = 1 是指在第二行出现，times = 3 是指一共有3个此包裹号的信息\n",
    "\n",
    "# 所以要在第五行插入\n",
    "\n",
    "rows = position+times+1\n",
    "sht.range(f'{rows}:{rows}').api.Insert()\n",
    "\n",
    "# 然后再写入更新的信息\n",
    "\n",
    "sht.range(f'a{rows}').value = extra\n",
    "# 大功告成！保存\n",
    "\n",
    "wb.save()\n",
    "\n",
    "\n",
    "# 其实折腾了半天都是为了这个插入语句\n",
    "\n",
    "sht.range('单元格或行列').api.Insert()\n",
    "\n",
    "\n",
    "#插入列\n",
    "sht.range('a:a').api.Insert()\n",
    "#插入行\n",
    "sht.range('2:2').api.Insert()\n",
    "#插入单元格\n",
    "sht.range('b4').api.Insert()\n",
    "\n",
    "\n",
    "# xlwings就说到这里了，讲得还算清楚吗？"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:root] *",
   "language": "python",
   "name": "conda-root-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
